import psycopg2
import shutil
import glob
import re
import os

# get sentinel2 tile list
def get_google_tiles(province):
	con = psycopg2.connect(
        database="sentinel2",
        user="postgres",
        password="12345",
        host="192.168.2.172",
        port="5432")
	cur = con.cursor()
	sql = f"""
            SELECT "全国图幅接合表".name
            FROM china_sy
            JOIN "全国图幅接合表" 
            on st_intersects(china_sy.geom,"全国图幅接合表".geom)
            WHERE  china_sy.qhmc = '{province}' 
	    """
	cur.execute(sql)
	con.commit()
	tiles=[record[0] for record in cur.fetchall()]
	tiles = [tile.upper() for tile in tiles]
	cur.close()
	con.close()
	return tiles

# get sentinel2 tile list
def get_google_tiles_by_dsy(province,city):
	con = psycopg2.connect(
        database="sentinel2",
        user="postgres",
        password="12345",
        host="192.168.2.172",
        port="5432")
	cur = con.cursor()
	sql = f"""
            SELECT "全国图幅接合表".name
            FROM china_dsy
            JOIN "全国图幅接合表" 
            on st_intersects(china_dsy.geom,"全国图幅接合表".geom)
            WHERE  china_dsy.qhmc = '{city}' 
            AND china_dsy.sjxzqdm in (
                SELECT qhdm
                FROM china_sy
                WHERE qhmc = '{province}'
            )
	    """
	cur.execute(sql)
	con.commit()
	tiles=[record[0] for record in cur.fetchall()]
	tiles = [tile.upper() for tile in tiles]
	cur.close()
	con.close()
	return tiles

# get sentinel2 tile list
def get_clip_vector(tiles):
    # con = psycopg2.connect(
    #     database="sentinel2",
    #     user="postgres",
    #     password="12345",
    #     host="192.168.2.172",
    #     port="5432")
    # cur = con.cursor()
    sql = f"""SELECT FROM "全国图幅接合表" WHERE "全国图幅接合表".name = any('{tiles}')"""
    print(sql)

    database="sentinel2"
    username="postgres"
    password="12345"
    host="192.168.2.172"
    port="5432"
    file_path = r""
    params = [
        f"pgsql2shp",
        f"-f {file_path}",
        f"-u {username}",
        f"-P {password}",
        f"-h {host}",
        f"-p {port}",
        f"{database}",
        f"\'{sql}\'"
    ]
    command = " ".join(params)
    print(command)
    # cur.execute(sql)
    # con.commit()
    # tiles=[record[0] for record in cur.fetchall()]
    # tiles = [tile.upper() for tile in tiles]
    # cur.close()
    # con.close()
    # print(tiles)
    return tiles


def main():
    ''''''
    # province = "江西省"
    # city = "赣州市"
    # folder = r"Z:\A01Image\Google_image\36_JiangXi\2022\新添"
    # os.system(r"net use Z: \\192.168.2.172\root DELL123dell /user:root /PERSISTENT:YES")
    # save_dir = r"Z:\A01Image\Google_image\36_JiangXi\2022\赣州市\10"
    # tiles = get_google_tiles_by_dsy(province, city)
    # for root, dir_name, file_names in os.walk(folder):
    #     for file_name in file_names:
    #         if re.match("^.*(\.jp2|\.tif)$",file_name):
    #             if os.path.splitext(file_name)[0] in tiles:
    #                 path = os.path.join(root,file_name)
    #                 target_path = os.path.join(save_dir, file_name)
    #                 if not os.path.exists(target_path):
    #                     shutil.move(path, target_path)
    #                     print(path)     
    ''''''
    # province = "江西省"
    # city = "赣州市"
    # tiles = get_google_tiles_by_dsy(province, city)
    # print(len(tiles))
    ''''''
    # tiles = get_google_tiles("江西省")
    # print(len(tiles))
    ''''''
    source_folders = [
        r"Z:\A01Image\Google_image\36_JiangXi\2022\赣州市\5",
        r"Z:\A01Image\Google_image\36_JiangXi\2022\赣州市\1",
        r"Z:\A01Image\Google_image\36_JiangXi\2022\赣州市\9",
    ]
    tiles = []
    for folder in source_folders:
        for name in os.listdir(folder):
            tile = os.path.splitext(name)[0]
            tiles.append(tile)
    tilestr = "{"+",".join(tiles)+"}"
    get_clip_vector(tilestr)
    return

if __name__ == "__main__":
    # os.system("net use Z: \\192.168.2.172\root DELL123dell /user:root")
    main()
